R Notebook

Avg number of beds each season

library(tidyverse)
library(lubridate)
library(ggplot2)

beds <- read_csv(here::here("raw_data/beds_by_nhs_board_of_treatment_and_specialty.csv")) %>% 
        janitor::clean_names()

Rows: 30448 Columns: 20
-- Column specification ----------------------------------------------------------------------------------------------------------------------
Delimiter: ","
chr (11): Quarter, QuarterQF, HB, HBQF, Location, LocationQF, Specialty, SpecialtyQF, SpecialtyName, SpecialtyNameQF, PercentageOccupancyQF
dbl  (5): AllStaffedBeds, TotalOccupiedBeds, AverageAvailableStaffedBeds, AverageOccupiedBeds, PercentageOccupancy
lgl  (4): AllStaffedBedsQF, TotalOccupiedBedsQF, AverageAvailableStaffedBedsQF, AverageOccupiedBedsQF

i Use `spec()` to retrieve the full column specification for this data.
i Specify the column types or set `show_col_types = FALSE` to quiet this message.

head(beds)

# interested in percentage of available beds
# data is structured to show avg daily percentage of available beds per hospital (id = "hb")
# comparing against the time of year "quarter"

Cleaning

# checking column is clean
beds %>% 
  count(quarter)

beds_season <- beds %>% 
  mutate(
    season = str_extract(quarter, "\\d{1}$"),
    season = recode(season, 
      "1" = "Winter",
      "2" = "Spring",
      "3" = "Summer",
      "4" = "Autumn"
    ),
    year = as.numeric(str_extract(quarter, "^\\d{4}"))
  )

Making Graph

beds_season %>% 
  group_by(season) %>% 
  summarise(avg_daily_beds_perc = mean(percentage_occupancy, na.rm = TRUE)) %>% 
  ggplot() +
  aes(x = season, y = avg_daily_beds_perc, label = str_c(round(avg_daily_beds_perc), "%")) +
  geom_text(nudge_y = 1, alpha = 0.8) +
  geom_col(fill = "turquoise", alpha = 0.8) +
  coord_cartesian(ylim = c(60, 80)) +
  theme_minimal()

beds_season %>% 
  group_by(season, year) %>% 
  summarise(avg_daily_beds_perc = mean(percentage_occupancy, na.rm = TRUE)) %>% 
  ggplot() +
  aes(x = season, y = avg_daily_beds_perc, label = str_c(round(avg_daily_beds_perc), "%")) +
  geom_text(nudge_y = 1.2, alpha = 0.8) +
  geom_col(fill = "turquoise", alpha = 0.8) +
  coord_cartesian(ylim = c(60, 80)) +
  facet_wrap(~year) +
  theme_minimal()

`summarise()` has grouped output by 'season'. You can override using the `.groups` argument.

Avg number of admissions each season (can be split by simd rating)

admissions <- read_csv(here::here("raw_data/hospital_admissions_hb_simd_20220302.csv")) %>% 
        janitor::clean_names()

Rows: 21138 Columns: 9
-- Column specification ----------------------------------------------------------------------------------------------------------------------
Delimiter: ","
chr (4): HB, HBQF, AdmissionType, AdmissionTypeQF
dbl (5): WeekEnding, SIMDQuintile, NumberAdmissions, Average20182019, PercentVariation

i Use `spec()` to retrieve the full column specification for this data.
i Specify the column types or set `show_col_types = FALSE` to quiet this message.

head(admissions)

Cleaning

admissions_date <- admissions %>% 
  mutate(
    year = str_extract(week_ending, "^\\d{4}"),
    monthday = str_extract(week_ending, "\\d{4}$"),
    month = str_extract(monthday, "^\\d{2}"),
    day = str_extract(monthday, "\\d{2}$"),
    date = ymd(str_c(year, month, day)),
    .before = 1
  ) %>% 
  select(-monthday)

head(admissions_date)